-- **************************************************************************
--    Project Name:   卸车差异明细(应卸和未卸)
--    Job Name:       jms_hi_dm__dm_shipment_stop_diff_unloading_detail_ht
--    Description :  任务经停表为主表,和装车关联获取的票数为应卸,和卸车表关联,关联上的为已卸
--    Author :        申家明
--    date：          2022/03/15
-- **************************************************************************
--    Relation ：
--    jms_dm.dm_shipment_stop_diff_unloading_detail_ht << [
--        jms_ods.yl_tmsnew_tms_shipment_stop_hf,
--        jms_ods.yl_tmsnew_tms_shipment_hf,
--        jms_ods.yl_lmdm_sys_network_expand_hf,
--        jms_ods.yl_lmdm_sys_network_expand_hf,
--        jms_ods.tab_barscan_loading_hi,
--        jms_ods.tab_barscan_unloading_hi
--    ]
-- **************************************************************************
--
--
-- **************************************************************************

insert overwrite table jms_dm.dm_shipment_stop_diff_unloading_detail_ht partition (dt)
select scantime           --卸货完成时间 --日期
     , agent_code         --代理区编码
     , agent_name         --代理区名称
     , fran_code
     , fran_name
     , network_code       --中心编码
     , network_name       --中心名称
     , shipment_no        --任务单号
     , start_network_code --始发网点编码
     , start_network_name --始发网点名称
     , pre_network_code   --上一站编码
     , pre_network_name   --上一站编码名称
     , shipment_name      --线路
     , billcode           --运单编号
     , package_code       --所属袋号
     , inputsite          --装车站点
     , loading_flag       --应卸标签 0代表没有应卸,1代表有应卸
     , unloading_flag     --未卸标签 1代表未卸
     , count_type         --网点类型
     , from_unixtime(unix_timestamp()) as update_time
     , '{{ execution_date | cst_hour }}'                 as dt
from (
         select a.scantime                                                                                                          --卸货完成时间 --日期
              , a.agent_code                                                                                                        --代理区编码
              , a.agent_name                                                                                                        --代理区名称
              , a.fran_code
              , a.fran_name
              , a.network_code                                                                                                      --中心编码
              , a.network_name                                                                                                      --中心名称
              , a.shipment_no                                                                                                       --任务单号
              , a.start_network_code                                                                                                --始发网点编码
              , a.start_network_name                                                                                                --始发网点名称
              , a.pre_network_code                                                                                                  --上一站编码
              , a.pre_network_name                                                                                                  --上一站编码名称
              , a.shipment_name                                                                                                     -- 线路
              , a.count_type                                                                                                        --网点类型
              , a.billcode                                                                                                          --运单编号
              , a.package_code                                                                                                      --所属袋号
              , a.inputsite                                                                                                         --装车站点
              , if(a.billcode is null, 0, 1)                                                                      as loading_flag   --应卸标签 0代表没有应卸,1代表有应卸
              , if(b.billcode is null, 1, 0)                                                                      as unloading_flag --未卸标签 1代表未卸
              , row_number() over (partition by a.billcode,a.network_code,a.shipment_no order by a.scantime desc) as rn
         from (
                  select scantime
                       , agent_code
                       , agent_name
                       , fran_code
                       , fran_name
                       , network_code
                       , network_name
                       , start_network_code
                       , start_network_name
                       , pre_network_code
                       , pre_network_name
                       , shipment_no
                       , shipment_name
                       , count_type
                       , billcode
                       , package_code
                       , inputsite
                  from (
                           select /*+ BROADCAST(a) */ a.scantime                                                         --   日期
                                , a.agent_code                                                                           --代理区编码
                                , a.agent_name                                                                           --代理区名称
                                , a.fran_code
                                , a.fran_name
                                , a.network_code                                                                         --中心编码
                                , a.network_name                                                                         --中心名称
                                , a.start_network_code                                                                   --始发网点编码
                                , a.start_network_name                                                                   --始发网点名称
                                , a.pre_network_code                                                                     --上一站编码
                                , a.pre_network_name                                                                     --上一站编码名称
                                , a.shipment_name                                                                        --任务单名称   --线路
                                , a.count_type                                                                           --网点类型
                                , a.shipment_no                                                          as shipment_no
                                , b.billcode                                                                             --运单编号
                                , b.packagecode                                                          as package_code --所属袋号
                                , b.inputsite
                                , b.scantime                                                             as load_scantime
                                , row_number()
                                   over (partition by b.billcode,a.shipment_no order by b.scantime desc) as rm
                           from (
                                    select /*+ BROADCAST(s1) */ substr(s2.unload_start_time, 1, 10) as scantime           --开始卸货时间为 扫描时间
                                         , ex.agent_code                                            as agent_code         --代理区编码
                                         , ex.agent_name                                            as agent_name         --代理区名称
                                         , fran_code                                                                      --加盟商编码
                                         , fran_name                                                                      --加盟商名称
                                         , s2.network_code                                          as network_code       --中心编码
                                         , ex.name                                                  as network_name       --中心名称
                                         , s3.shipment_no                                           as shipment_no        --任务单号
                                         , s3.start_code                                            as start_network_code --始发网点编码
                                         , s3.start_name                                            as start_network_name --始发网点名称
                                         , pre.network_code                                         as pre_network_code   --上一站编码
                                         , pre.network_name                                         as pre_network_name   --上一站编码名称
                                         , s3.shipment_name                                         as shipment_name      --任务单名称   --线路
                                         , case
                                               when network_type = 4 then 1
                                               when network_type = 5 then 1
                                               else 3
                                        end                                                         as count_type         --1代表中心,2代表集散,3代表网点非集散
                                    from (
                                             select sort_num     --顺序编号
                                                  , shipment_id  --任务id
                                                  , network_code --经停网点编号
                                                  , network_name --经停网点名称
                                             from jms_dwd.dwd_tmsnew_shipment_stop_union_base_hi --任务经停表
                                             where dt >= date_add('{{ execution_date  | cst_ds }}', -90)
                                               and network_name not like '测试%'
                                               and table_type = 1
                                             group by sort_num     --顺序编号
                                                    , shipment_id  --任务id
                                                    , network_code --经停网点编号
                                                    , network_name --经停网点名称
                                         ) s1
                                             inner join
                                         (
                                             select sort_num          --顺序编号
                                                  , shipment_id       --任务id
                                                  , network_code      --经停网点编号
                                                  , unload_start_time --卸货开始
                                             from jms_dwd.dwd_tmsnew_shipment_stop_union_base_hi t --任务经停表
                                             where dt >= date_add('{{ execution_date  | cst_ds }}', -90)
                                               and network_name not like '测试%'
                                               and substr(t.unload_start_time, 1, 10) <=
                                                   date_add('{{ execution_date  | cst_ds }}', 1)
                                               and substr(t.unload_start_time, 1, 10) >=
                                                   date_add('{{ execution_date  | cst_ds }}', -1)
                                               and (actual_arrival_time is not null or
                                                    unlock_time is not null or
                                                    t.unload_start_time is not null)
                                               and table_type = 1
                                             group by sort_num     --顺序编号
                                                    , shipment_id  --任务id
                                                    , network_code --经停网点编号
                                                    , unload_start_time --卸货开始
                                         ) s2
                                         on s1.sort_num + 1 = s2.sort_num
                                             and s1.shipment_id = s2.shipment_id
                                             inner join
                                         (
                                             select shipment_no   --任务编号
                                                  , shipment_name --任务名称
                                                  , start_code    --始发网点编号
                                                  , start_name    --始发网点名称
                                                  , id            --主键
                                             from jms_dwd.dwd_tmsnew_shipment_union_base_hi --任务表
                                             where dt >= date_add('{{ execution_date  | cst_ds }}', -90)
                                               and shipment_state <> 0
                                               and shipment_state <> 5
                                               and table_type = 1
                                             group by shipment_no   --任务编号
                                                    , shipment_name --任务名称
                                                    , start_code    --始发网点编号
                                                    , start_name    --始发网点名称
                                                    , id --主键
                                         ) s3
                                         on s1.shipment_id = s3.id
                                             left join
                                         (
                                             select code       --网点编码
                                                  , name       --网点名称
                                                  , agent_code --所属代理区code
                                                  , agent_name --所属代理区名称
                                                  , fran_code  --加盟商编码
                                                  , fran_name  --加盟商名称
                                                  , network_type
                                             from jms_dim.dim_network_whole_massage --基础网点信息合并
                                         ) ex
                                         on s2.network_code = ex.code
                                             left join
                                         (
                                             select sort_num     --顺序编号
                                                  , shipment_no
                                                  , network_code --经停网点编号
                                                  , network_name --经停网点名称
                                             from jms_dwd.dwd_tmsnew_shipment_stop_union_base_hi t --任务经停表
                                             where dt >= date_add('{{ execution_date  | cst_ds }}', -90)
                                               and network_name not like '测试%'
                                               and table_type = 1
                                             group by sort_num     --顺序编号
                                                    , shipment_no
                                                    , network_code --经停网点编号
                                                    , network_name --经停网点名称
                                         ) pre
                                         on pre.shipment_no = s3.shipment_no
                                             and pre.sort_num = s2.sort_num - 1
                                ) a
                                    left join
                                (
                                    select shipment_no  as transfercode --转单号
                                         , waybill_no   as billcode     --运单编号
                                         , package_code as packagecode  --所属袋号
                                         , scan_site    as inputsite    --录入部门
                                         , next_station_code as nextstationcode              --上下一站编号
                                         , scan_time    as scantime     --扫描时间
                                    from jms_dwd.dwd_tab_barscan_loading_base_hi --装车表
                                    where substr(dt, 1, 10) <= date_add('{{ execution_date  | cst_ds }}',1)
                                      and substr(dt, 1, 10) >=
                                          date_add('{{ execution_date  | cst_ds }}', -6)
                                      and waybill_no like 'JT%'
                                ) b
                                on a.shipment_no = b.transfercode
                                    and a.network_code = b.nextstationcode
                       ) t
                  where rm = 1
              ) a
                  left join
              (
                  SELECT waybill_no     as billcode
                       , scan_site_code as scansitecode
                       , shipment_no    as transfercode
                       , scan_time      as scantime
                  FROM jms_dwd.dwd_tab_barscan_unloading_base_hi --卸车表
                  where substr(dt, 1, 10) <= date_add('{{ execution_date  | cst_ds }}',1)
                    and substr(dt, 1, 10) >= date_add('{{ execution_date  | cst_ds }}', -7)
              ) b
              on a.billcode = b.billcode
                  and a.network_code = b.scansitecode
     ) a
where rn = 1
    distribute by dt
    , pmod(hash(billcode), 100);